import cron from 'node-cron';
import db from './mysqlHelper.js';
import logger from './logger.js';
import moment from 'moment';
import lsyPay from './common/lsyPay.js';


//每天凌晨1点生成日报表
cron.schedule('0 1 * * *', async () => {
    await clear_user_pay_rec();
    await make_day_report();
});
//每天凌晨执行自动提现
cron.schedule('0 0 * * *', async () => {
   // await auto_withdraw();
});

//每小时执行一次正在充电订单检查 确认是否有同一个桩的同一个枪同时有两个正在充电的订单 并根据正在充电的订单最后一次更新时间
//判断保留最后更新时间最近的订单以外的订单都作为异常订单处理
cron.schedule('0 * * * *', async () => {
    await check_charging_orders();
});

async function check_charging_orders() {
    console.log(`开始检查正在充电订单`);
    //检查是否有同一个桩的同一个枪同时有两个正在充电的订单
    let sql = `    SELECT *    FROM chargeing_order    WHERE (sn, guns_no) IN (
        SELECT sn, guns_no        FROM chargeing_order    GROUP BY sn, guns_no  HAVING COUNT(*) > 1    )
    ORDER BY last_update DESC,sn, guns_no   ;`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`异常订单巡检查询重复的充电订单出错 error :` + res.message);
        return;
    }
    if (res.length == 0) {
        console.log(`没有重复的充电订单`);
        return;
    }
    //定义异常订单数组
    let abnormal_orders = [];
    while (res.length > 0) {
        let first_order = res[0];
        //查询同一个桩的同一个枪并且id不等于第一个订单的所有订单
        let same_orders = res.filter(o => o.sn === first_order.sn && o.guns_no === first_order.guns_no && o.id !== first_order.id);
        if (same_orders.length > 0) {
            //将相同的订单加入到异常订单数组
            for (let o of same_orders) {
                abnormal_orders.push(o);
            }
        }
        //从res中除去与第一个订单相同的订单
        res = res.filter(o => o.sn !== first_order.sn || o.guns_no !== first_order.guns_no);
    }
    if (abnormal_orders.length == 0) {
        console.log(`没有异常订单`);
        return;
    }
    let sqls = [];
    for (let order of abnormal_orders) {
        sql = `select project_id,isp_id,pay_sn,pay_amt,plate,openid,path from user_pay_rec where phone='${order.phone}' 
            and pay_status=10 and pay_type=0 and project_id='${orderproject_id}' order by create_time desc limit 1`;
        res = await db.query(sql);
        if (res instanceof Error) {
            logger.error(`异常查询可退款的预充值付款记录出错 error :` + res.message, `phone:${phone}`, JSON.stringify(order));
            continue;
        }
        if (res.length == 0) {
            logger.error(`异常订单没有可退款的预充值付款记录`, `phone:${phone}`, JSON.stringify(order));
            continue;
        }
        let pay_sn = res[0].pay_sn;
        let pay_amt = res[0].pay_amt;
        let refund_amt = pay_amt;
        let sqls = [];
        if (refund_amt > 0) {
            //退款操作
            let create_time = moment().format('YYYY-MM-DD HH:mm:ss');
            let refund_sn = lsyPay.generateTraceNo();
            let refund_res = await lsyPay.refund(refund_sn, refund_amt, pay_sn);
            if (refund_res.code == 1) {
                logger.error(`退款失败`, `phone:${phone}, refund_amt:${refund_amt}, pay_sn:${pay_sn}`, JSON.stringify(order));
                continue
            }
            console.log('异常订单退款结果', JSON.stringify(refund_res));
            //开始构建事务更新数据库

            let refund_status = 10;
            if (refund_res.code == 2) refund_status = 0;
            //插入退款记录
            let refund_record = {
                phone: phone,
                refund_amt,
                refund_sn,
                refund_status,
                create_time,
                refund_type: 2, // 2:异常订单退款
                project_id: res[0].project_id,
                isp_id: res[0].isp_id,
                plate: res[0].plate,
                openid: res[0].openid,
            }
            if (refund_res.code == 0) {
                refund_record.refund_time = moment().format('YYYY-MM-DD HH:mm:ss');
            }
            sqls.push({ sql: `insert into user_refund_rec set ?`, param: [refund_record] });
            //更新付款记录的状态
            if (refund_res.code == 0) {
                sqls.push({ sql: `update user_pay_rec set pay_status=31,refund_sn=? where pay_sn=?`, param: [refund_sn, pay_sn] });
            } else if (refund_res.code == 2) {
                sqls.push({ sql: `update user_pay_rec set pay_status=30,refund_sn=? where pay_sn=?`, param: [refund_sn, pay_sn] });
            }
        } else {
            sqls.push({ sql: `update user_pay_rec set pay_status=40  where pay_sn=?`, param: [pay_sn] });//更新付款记录的状态
        }
        //删除异常的正在充电的订单
        sqls.push({ sql: `delete from chargeing_order where id=?`, param: [order.id] });
    }

    if (sqls.length == 0) {
        console.log(`没有需要处理的异常订单`);
        return;
    }
    //批量执行事务
    res = await db.transaction(sqls);
    if (res instanceof Error) {
        logger.error(`处理异常订单事务出错 error :` + res.message);
        return;
    }
    console.log(`处理异常订单完成，共处理 ${abnormal_orders.length} 个异常订单`);
    for (let order of abnormal_orders) {
        console.log(`异常订单处理完成，订单ID: ${order.id}, 手机号: ${order.phone}, SN: ${order.sn}, 枪号: ${order.guns_no}`);
    }

}

async function sleep(ms) {
    return new Promise((resolve) => {
        setTimeout(resolve, ms);
    });
}

//清除除了支付状态为10的 订单创建时间超过48小时的订单
async function clear_user_pay_rec() {
    let sql = `delete from user_pay_rec where pay_status!=10 and create_time<DATE_SUB(NOW(), INTERVAL 48 HOUR)`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`清除订单出错 error :` + res.message);
        return { code: 0, msg: '清除订单出错' };
    }
    if (res.affectedRows > 0) {
        logger.info(`清除无效订单成功`, `affectedRows:${res.affectedRows}`);
    }
}
//按项目生成日报表
async function make_day_report() {

    console.log(`开始生成日报表`);
    //获取所有的项目的id列表
    let sql = `select project.id,project.name,isp_id,project.path,isp.name as isp_name from project left join isp on project.isp_id=isp.id`;
    let project_infos = await db.query(sql);
    if (project_infos instanceof Error) {
        logger.error(`生成日报获取项目列表出错 error :` + project_infos.message);
        return;
    }
    let sqls = [];
    //遍历项目id列表，生成日报表
    for (let i = 0; i < project_infos.length; i++) {
        let project = project_infos[i];
        //从充电订单中获取总充电量  谷总充电量  峰充电量 平充电量 尖充电量 订单总条数 手续费 总充电费用  总服务费 以及总费用
        // 查询前一天0点到当天0点的数据
        const startTime = moment().subtract(1, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endTime = moment().startOf('day').format('YYYY-MM-DD HH:mm:ss');
        sql = `select sum(charge_quantity) as charge_quantity, sum(charge_quantity_g) as charge_quantity_g, sum(charge_quantity_f) as charge_quantity_f, 
        sum(charge_quantity_p) as charge_quantity_p, sum(charge_quantity_j) as charge_quantity_j, 
        count(*) as order_count, sum(commission) as commission, sum(amount) as amount,  sum(e_amt) as e_amt, sum(s_amt) as s_amt,sum(income) as income
        from charge_order   where project_id='${project.id}' and start_time >= '${startTime}' and start_time < '${endTime}'`;
        let day_info = await db.query(sql);
        if (day_info instanceof Error) {
            logger.error(`生成日报获取项目数据出错 error :` + day_info.message);
            continue;
        }

        let day_report = {
            date:moment().subtract(1, 'days').startOf('day').format('YYYY-MM-DD'),
            project_id: project.id,
            name: project.name,
            isp_id: project.isp_id,
            path: project.path,
            isp_name: project.isp_name,
            amt: day_info[0].amount,
            charge_quantity: day_info[0].charge_quantity,
            charge_quantity_g: day_info[0].charge_quantity_g,
            charge_quantity_f: day_info[0].charge_quantity_f,
            charge_quantity_p: day_info[0].charge_quantity_p,
            charge_quantity_j: day_info[0].charge_quantity_j,
            order_count: day_info[0].order_count,
            commission_amt: day_info[0].commission,
            e_amt: day_info[0].e_amt,
            s_amt: day_info[0].s_amt,
            income: day_info[0].income
        }
        sqls.push({ sql: `insert charge_day_record set ?`, param: day_report });
    }
    //检查当前天是否为一个月的第一天，如果是，则生成当月的日报表
    let today = moment().startOf('day');
    if (today.date() === 1) {
        console.log(`今天是一个月的第一天，开始生成月报表`);
        // 计算上个月的起止时间
        const startOfLastMonth = today.clone().subtract(1, 'months').startOf('month').format('YYYY-MM-DD HH:mm:ss');
        const endOfLastMonth = today.clone().subtract(1, 'months').endOf('month').format('YYYY-MM-DD HH:mm:ss');
        // 查询上个月的日报表数据，按项目聚合
        let sql = `
            SELECT 
                project_id,
                name,
                isp_id,
                path,
                isp_name,
                DATE_FORMAT(MIN(date), '%Y-%m') as month,
                SUM(amt) as amt,
                SUM(charge_quantity) as charge_quantity,
                SUM(charge_quantity_g) as charge_quantity_g,
                SUM(charge_quantity_f) as charge_quantity_f,
                SUM(charge_quantity_p) as charge_quantity_p,
                SUM(charge_quantity_j) as charge_quantity_j,
                SUM(order_count) as order_count,
                SUM(commission_amt) as commission_amt,
                SUM(e_amt) as e_amt,
                SUM(s_amt) as s_amt,
                SUM(income) as income
            FROM charge_day_record
            WHERE date >= '${startOfLastMonth}' AND date <= '${endOfLastMonth}'
            GROUP BY project_id, name, isp_id, path, isp_name
        `;
        let month_reports = await db.query(sql);
        if (month_reports instanceof Error) {
            logger.error(`生成月报获取日报数据出错 error :` + month_reports.message);
        } else {
            for (let report of month_reports) {
                sqls.push({ sql: `insert into charge_month_record set ?`, param: report });
            }
           
        }
    }

    //批事务插入
    if (sqls.length == 0) {
        logger.warn(`没有日报数据需要插入`);
        return;
    }
    let res = await db.transaction(sqls);
    if (res instanceof Error) {
        console.error(`生成日报表 事务插入出错 error :` + res.message);

    }

}

//每天零点查询所有商户的余额并自动完成提现
async function auto_withdraw() {
    console.log(`开始自动提现`);
    //获取所有的商户的支付参数列表
    let sql = `select auto_withdraw_min,name,pay_param from merchant`;
    let pay_param_list = await db.query(sql);
    if (pay_param_list instanceof Error) {
        logger.error(`自动提现获取商户支付参数列表出错 error :` + pay_param_list.message);
        return;
    }
    //遍历商户支付参数列表，查询商户余额并自动完成提现
    for (let i = 0; i < pay_param_list.length; i++) {
        if (pay_param_list[i].pay_param == null || pay_param_list[i].pay_param == '') {
            continue;
        }
        let balance = await lsyPay.getMerchantBalance(pay_param_list[i].pay_param);
        if (balance.code != 0) {
            logger.error(`自动提现获取商户余额出错 error :` + balance.msg);
            continue;
        }
        let min_withdraw_amt = 0;
        if (pay_param_list[i].auto_withdraw_min != null) {
            min_withdraw_amt = pay_param_list[i].auto_withdraw_min;
        }
        let withdraw_amt = balance.balance - min_withdraw_amt;
        if (withdraw_amt <= 0) {
            console.log(` 商户 ${pay_param_list[i].name}  余额不足 ${balance.balance / 100}}，最小提现金额为 ${min_withdraw_amt / 100}， 无需提现`);
        }
        let withdraw_result = await lsyPay.merchantWithdraw(pay_param_list[i].pay_param, withdraw_amt);
        if (withdraw_result.code > 0) {
            logger.error(`自动提现出错 error :` + withdraw_result.msg);
            continue;
        }
        console.log(` 商户 ${pay_param_list[i].name}  提现成功 ${withdraw_amt / 100}}`);
    }
    console.log(`自动提现完成`);
}

//  make_day_report();
export default {

};

